﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using NPOI.Util;
using Simple.Common.Authentication;
using Simple.Repository.Models.Attendance;

namespace Simple.Services.System
{
    public class ChildAttendanceService : BusinessBaseService
    {
        private readonly SimpleDbContext _context;

        public ChildAttendanceService(SimpleDbContext context,
            ISimpleService services) : base(services)
        {
            _context = context;
        }

        public async Task<PageResultModel<ChildAttendanceModel>> GetPageAsync(ChildAttendancePageInputModel input)
        {
            var result = new PageResultModel<ChildAttendanceModel>();
            var query = await OrganizationFilter(_context.Set<Childrens>().AsQueryable().Where(p => p.ChildState == EnumChildState.Reading));

            // 根据条件查询
            if (input.ClassIds != null && input.ClassIds.Any())
            {
                query = query.Where(u => input.ClassIds.Contains(u.ClassesId));
            }
            if (!string.IsNullOrEmpty(input.ChildName))
            {
                query = query.Where(u => u.Name.Contains(input.ChildName));
            }

            // 获取总数量
            result.TotalRows = await query.CountAsync();

            // 分页查询
            query = query.OrderBy(u => u.CreatedTime).Page(input.PageNo, input.PageSize);
            var lstChild = await query.ToListAsync();
            var lstChildAttendanceDto = MapperHelper.Map<List<ChildAttendanceModel>>(lstChild);
            var lstStudentNub = lstChildAttendanceDto.Select(p => p.StudentNumber).ToList();
            var lstAttendanceRecord = _context.Set<AttendanceRecord>()
                .Where(c => lstStudentNub.Contains(c.UserNumber))
                .Where(c => c.AttendanceTime.Year == input.Month.Value.Year && c.AttendanceTime.Month == input.Month.Value.Month).ToList();
            if (lstChildAttendanceDto != null && lstChildAttendanceDto.Any())
            {
                var hhp = HolidayHelper.GetInstance();
                var holidayAndWorkList = hhp.GetWorkDayAndHolidayByMonth(input.Month.Value);//国家法定节假日和工作日
                var should = holidayAndWorkList.Item1.Count;//应出
                var ruleDate = _context.Set<ChildAttendanceRule>().OrderByDescending(p => p.CreatedTime).FirstOrDefault();
                var firstDateTime = DateTime.Now;
                var lastDateTime = DateTime.Now;
                if (ruleDate != null)
                {
                    if (ruleDate.EntryTime.HasValue)
                        firstDateTime = Convert.ToDateTime($"{ruleDate?.EntryTime.Value.Hour}:{ruleDate?.EntryTime.Value.Minute}");
                    if (ruleDate.OutSchoolTime.HasValue)
                        lastDateTime = Convert.ToDateTime($"{ruleDate?.OutSchoolTime.Value.Hour}:{ruleDate?.OutSchoolTime.Value.Minute}");
                }
                foreach (var item in lstChildAttendanceDto)
                {

                    var reality = 0;//实际出勤
                    var noCheckIn = 0; //却勤
                    item.Should = should;
                    foreach (var workDay in holidayAndWorkList.Item1)
                    {
                        var lstRecordDate = lstAttendanceRecord.Where(p => p.AttendanceTime.Year == workDay.Year && p.AttendanceTime.Month == workDay.Month && p.AttendanceTime.Day == workDay.Day && p.UserNumber == item.StudentNumber).OrderBy(p => p.AttendanceTime).ToList();
                        if (lstRecordDate != null && lstRecordDate.Any())
                        {
                            var _firstDate = Convert.ToDateTime($"{lstRecordDate.FirstOrDefault()?.AttendanceTime.Hour}:{lstRecordDate.FirstOrDefault()?.AttendanceTime.Minute}");
                            var _lastDate = Convert.ToDateTime($"{lstRecordDate.LastOrDefault()?.AttendanceTime.Hour}:{lstRecordDate.LastOrDefault()?.AttendanceTime.Minute}");

                            if (DateTime.Compare(_firstDate, firstDateTime) <= 0 && DateTime.Compare(_lastDate, lastDateTime) >= 0)
                            {
                                // 有效打卡
                                reality++;
                                item.AttendanceRecordList.Add(lstRecordDate.FirstOrDefault());
                                item.AttendanceRecordList.Add(lstRecordDate.LastOrDefault());
                            }
                            else
                            {
                                // 无效打卡（迟到、早退）
                            }
                        }
                        else
                        {
                            //缺卡
                            noCheckIn++;
                        }
                    }
                    item.Reality = reality;
                    item.NoCheckIn = noCheckIn;
                }
            }

            result.Rows = lstChildAttendanceDto;

            result.SetPage(input);
            result.CountTotalPage();

            return result;
        }

        public async Task<List<ChildAttendanceDetailDto>> GetDetailAsync(DetailInput input)
        {
            var result = new List<ChildAttendanceDetailDto>();
            var hhp = HolidayHelper.GetInstance();
            var holidayAndWorkList = hhp.GetWorkDayAndHolidayByMonth(input.Month.Value);//国家法定节假日和工作日
            // 考勤规则日期
            var ruleDate = await _context.Set<ChildAttendanceRule>().OrderByDescending(p => p.CreatedTime).FirstOrDefaultAsync();
            var firstDateTime = string.Empty;
            var lastDateTime = string.Empty;
            if (ruleDate != null)
            {
                if (ruleDate.EntryTime.HasValue)
                    firstDateTime = ruleDate.EntryTime.Value.ToString("H:m:s");
                if (ruleDate.OutSchoolTime.HasValue)
                    lastDateTime = ruleDate.OutSchoolTime.Value.ToString("H:m:s");
            }
            var lstAttendanceRecord = _context.Set<AttendanceRecord>()
                .Where(c => c.UserNumber == input.StudentNumber)
                .Where(c => c.AttendanceTime.Year == input.Month.Value.Year && c.AttendanceTime.Month == input.Month.Value.Month).ToList();
            foreach (var workDay in holidayAndWorkList.Item1)
            {
                if (workDay > DateTime.Now) continue;
                var lstRecordDate = lstAttendanceRecord.Where(p => p.AttendanceTime.Day == workDay.Day).OrderBy(p => p.AttendanceTime).ToList();
                if (lstRecordDate != null && lstRecordDate.Any())
                {
                    var firstRule = DateTime.Parse($"{workDay.Year}-{(workDay.Month).ToString().PadLeft(2, '0')}-{workDay.Day.ToString().PadLeft(2, '0')} {firstDateTime ?? "08:30"}");// 规则设置中最早打卡时间
                    var lastRule = DateTime.Parse($"{workDay.Year}-{(workDay.Month).ToString().PadLeft(2, '0')}-{workDay.Day.ToString().PadLeft(2, '0')} {lastDateTime ?? "17:30"}"); //规则设置中最晚打卡时间

                    var _firstDate = lstRecordDate.Where(x => x.EntranceType == 1).FirstOrDefault()?.AttendanceTime;
                    var _lastDate = lstRecordDate.Where(x => x.EntranceType == 2).FirstOrDefault()?.AttendanceTime;

                    var shangbanTitle = "缺卡";
                    if (_firstDate.HasValue)
                    {
                        shangbanTitle = $"{_firstDate.Value.ToString("H:m:s")} {(_firstDate > firstRule ? "迟到" : "")}";
                    }
                    //入园
                    result.Add(new ChildAttendanceDetailDto()
                    {
                        Title = shangbanTitle,
                        Start = workDay.ToString("yyyy-MM-dd"),
                        End = workDay.ToString("yyyy-MM-dd"),
                        CssClass = "blue"
                    });


                    var xiabanTitle = "缺卡";
                    if (_lastDate != null)
                    {
                        xiabanTitle = $"{_lastDate.Value.ToString("H:m:s")} {(_lastDate < lastRule ? "早退" : "")}";
                    }
                    //下班
                    result.Add(new ChildAttendanceDetailDto
                    {
                        Title = xiabanTitle,
                        Start = $"{workDay.ToString("yyyy-MM-dd")}",
                        End = $"{workDay.ToString("yyyy-MM-dd")}",
                        CssClass = "'red'"
                    });
                }
                else
                {
                    //缺卡
                    result.Add(new ChildAttendanceDetailDto()
                    {
                        Title = "缺卡",
                        Start = workDay.ToString("yyyy-MM-dd"),
                        End = workDay.ToString("yyyy-MM-dd"),
                        CssClass = "'red'"
                    });
                    result.Add(new ChildAttendanceDetailDto()
                    {
                        Title = "缺卡",
                        Start = workDay.ToString("yyyy-MM-dd"),
                        End = workDay.ToString("yyyy-MM-dd"),
                        CssClass = "'red'"
                    });
                }
            }
            return result;
        }

        public async Task<MobileChildAttendanceDto> MobileChildAttendanceStats()
        {
            var result = new MobileChildAttendanceDto();
            var userId = _currentUser?.FindClaimValue(SimpleClaimTypes.UserId);
            if (userId == null)
            {
                return result;
            }
            var lstClassId = (await OrganizationFilter(_context.Set<SysClasses>().AsQueryable())).Where(p => p.TeacherIds.Contains(userId) && p.IsArchived == false).Select(p => p.Id.ToString()).ToList();
            var lstStudentNub = _context.Set<Childrens>().AsQueryable().Where(p => lstClassId.Contains(p.ClassesId)).Select(p => p.StudentNumber).ToList();
            if (lstStudentNub == null)
            {
                return result;
            }
            result.Attendance = await Attendance(lstStudentNub);
            var now = DateTime.Now.Date;
            var lstAttendanceRecordByDate = _context.Set<AttendanceRecord>()
                .Where(c => lstStudentNub.Contains(c.UserNumber))
                .Where(c => c.AttendanceTime.Date == now.Date).ToList();

            var hhp = HolidayHelper.GetInstance();
            var holidayAndWorkList = hhp.GetWorkDayAndHolidayByMonth(now);//国家法定节假日和工作日
            if (!holidayAndWorkList.Item1.Any(p => p.Date == now))
            {
                result.CheckInCount = lstStudentNub.Count;
                return result;
            }
            if (!lstAttendanceRecordByDate.Any())
            {
                result.NotCheckOutCount = lstStudentNub.Count;
                return result;
            }
            foreach (var item in lstStudentNub)
            {
                var lstRecordDate = lstAttendanceRecordByDate.Where(p => p.UserNumber == item).OrderBy(p => p.AttendanceTime).ToList();
                if (lstRecordDate != null && lstRecordDate.Any())
                {
                    result.CheckInCount++;
                }
                else
                {
                    result.NotCheckOutCount++;
                }
            }
            return result;
        }

        /// <summary>
        /// 学生本月出勤率
        /// </summary>
        /// <returns></returns>
        private async Task<string> Attendance(List<string>? lstStudentNub)
        {
            if (lstStudentNub == null)
            {
                return String.Empty;
            }

            var now = DateTime.Now.Date;
            var lstAttendanceRecordByMonth = _context.Set<AttendanceRecord>()
                .Where(c => lstStudentNub.Contains(c.UserNumber))
                .Where(c => c.AttendanceTime.Year == now.Year && c.AttendanceTime.Month == now.Month).ToList();
            // 考勤规则日期
            if (lstAttendanceRecordByMonth == null || !lstAttendanceRecordByMonth.Any())
            {
                return "0%";
            }
            var hhp = HolidayHelper.GetInstance();
            var holidayAndWorkList = hhp.GetWorkDayAndHolidayByMonth(now);//国家法定节假日和工作日
            decimal total = holidayAndWorkList.Item1.Count * lstStudentNub.Count;
            decimal checkInCount = 0;
            foreach (var workDay in holidayAndWorkList.Item1)
            {
                foreach (var item in lstStudentNub)
                {
                    var lstRecordDate = lstAttendanceRecordByMonth.Where(p => p.UserNumber == item && p.AttendanceTime.Date == workDay.Date).OrderBy(p => p.AttendanceTime).ToList();
                    if (lstRecordDate != null && lstRecordDate.Any())
                    {
                        checkInCount++;
                    }
                }
            }
            return $"{(checkInCount / total * 100).ToString("0.00")}%";
        }

        public async Task<MobileAdmissionRecordDto> MobileAdmissionRecord()
        {
            var result = new MobileAdmissionRecordDto();
            var now = DateTime.Now.Date;
            var childId = _currentUser?.FindClaimValue(SimpleClaimTypes.ChildId);
            if (childId == null || string.IsNullOrWhiteSpace(childId)) return result;
            var child = await _context.Set<Childrens>().AsQueryable().FirstOrDefaultAsync(p => p.Id.ToString() == childId);
            if (child == null) return result;

            var latAttendanceRecordByMonth = _context.Set<AttendanceRecord>()
                .Where(c => c.UserNumber == child.StudentNumber)
                .Where(c => c.AttendanceTime.Date == now.Date).OrderBy(p => p.AttendanceTime).ToList();

            if (latAttendanceRecordByMonth != null)
            {
                var first = latAttendanceRecordByMonth.FirstOrDefault();
                var last = latAttendanceRecordByMonth.LastOrDefault();
                if (first != null)
                {
                    result.Start = first.AttendanceTime.ToString("HH:mm");
                }
                if (last != null && latAttendanceRecordByMonth.Count > 1)
                {
                    result.End = last.AttendanceTime.ToString("HH:mm");

                }
            }
            return result;
        }

        public async Task<List<MobileAdmissionRecordDetailDto>> MobileAdmissionRecordDetail(MobileChildAttendanceInput input)
        {
            var result = new List<MobileAdmissionRecordDetailDto>();
            var now = DateTime.Now.Date;
            var childId = _currentUser?.FindClaimValue(SimpleClaimTypes.ChildId);
            if (childId == null || string.IsNullOrWhiteSpace(childId)) return result;
            var child = await _context.Set<Childrens>().AsQueryable().FirstOrDefaultAsync(p => p.Id.ToString() == childId);
            if (child == null) return result;
            var lstAttendanceRecord = _context.Set<AttendanceRecord>()
                .Where(c => c.UserNumber == child.StudentNumber)
                .Where(c => c.AttendanceTime.Year == input.Month.Value.Year && c.AttendanceTime.Month == input.Month.Value.Month).ToList();

            var hhp = HolidayHelper.GetInstance();
            var holidayAndWorkList = hhp.GetWorkDayAndHolidayByMonth(input.Month.Value);//国家法定节假日和工作日
            // 考勤规则日期
            var ruleDate = await _context.Set<ChildAttendanceRule>().OrderByDescending(p => p.CreatedTime).FirstOrDefaultAsync();
            var firstDateTime = DateTime.Now;
            var lastDateTime = DateTime.Now;
            if (ruleDate != null)
            {
                if (ruleDate.EntryTime.HasValue)
                    firstDateTime = Convert.ToDateTime($"{ruleDate?.EntryTime.Value.Hour}:{ruleDate?.EntryTime.Value.Minute}");
                if (ruleDate.OutSchoolTime.HasValue)
                    lastDateTime = Convert.ToDateTime($"{ruleDate?.OutSchoolTime.Value.Hour}:{ruleDate?.OutSchoolTime.Value.Minute}");
            }
            foreach (var workDay in holidayAndWorkList.Item1)
            {
                if (workDay > DateTime.Now) continue;
                var lstRecordDate = lstAttendanceRecord.Where(p => p.AttendanceTime.Day == workDay.Day).OrderBy(p => p.AttendanceTime).ToList();
                if (lstRecordDate != null && lstRecordDate.Any())
                {
                    var _firstDate = Convert.ToDateTime($"{lstRecordDate.FirstOrDefault()?.AttendanceTime.Hour}:{lstRecordDate.FirstOrDefault()?.AttendanceTime.Minute}");
                    var _lastDate = Convert.ToDateTime($"{lstRecordDate.LastOrDefault()?.AttendanceTime.Hour}:{lstRecordDate.LastOrDefault()?.AttendanceTime.Minute}");

                    if (DateTime.Compare(_firstDate, firstDateTime) <= 0 && DateTime.Compare(_lastDate, lastDateTime) >= 0)
                    {
                        result.Add(new MobileAdmissionRecordDetailDto()
                        {
                            Date = workDay.ToString("yyyy-MM-dd"),
                            Info = "签到",
                            Data = new RecordDetailDataDto()
                            {
                                Shangbanka = _firstDate.ToString("HH:mm"),
                                Xiabanka = _lastDate.ToString("HH:mm")
                            }
                        });
                        continue;
                    }

                    if (DateTime.Compare(_firstDate, firstDateTime) <= 0 && DateTime.Compare(_lastDate, lastDateTime) < 0)
                    {
                        result.Add(new MobileAdmissionRecordDetailDto()
                        {
                            Date = workDay.ToString("yyyy-MM-dd"),
                            Info = "签到",
                            Data = new RecordDetailDataDto()
                            {
                                Shangbanka = _firstDate.ToString("HH:mm"),
                                Xiabanka = String.Empty,
                            }
                        });
                        continue;
                    }

                    if (DateTime.Compare(_firstDate, firstDateTime) > 0 && DateTime.Compare(_lastDate, lastDateTime) >= 0)
                    {
                        result.Add(new MobileAdmissionRecordDetailDto()
                        {
                            Date = workDay.ToString("yyyy-MM-dd"),
                            Info = "未签到",
                            Data = new RecordDetailDataDto()
                            {
                                Shangbanka = String.Empty,
                                Xiabanka = _lastDate.ToString("HH:mm"),
                            }
                        });
                        continue;
                    }
                }
                else
                {
                    //缺卡
                    result.Add(new MobileAdmissionRecordDetailDto()
                    {
                        Date = workDay.ToString("yyyy-MM-dd"),
                        Info = "未签到",
                        Data = new RecordDetailDataDto()
                    });
                }
            }
            return result;
        }

        public async Task<CheckedInDto> GetCheckedStats(GetCheckedStatsInput input)
        {
            var result = new CheckedInDto();
            var userId = _currentUser?.FindClaimValue(SimpleClaimTypes.UserId);
            if (userId == null)
            {
                return result;
            }
            var lstClass = (await OrganizationFilter(_context.Set<SysClasses>().AsQueryable())).Where(p => p.TeacherIds.Contains(userId) && p.IsArchived == false).ToList();
            var lstClassId = lstClass.Select(p => p.Id.ToString()).ToList();
            if (lstClass == null || !lstClass.Any())
            {
                return result;
            }
            var lstStudent = _context.Set<Childrens>().AsQueryable().Where(p => lstClassId.Contains(p.ClassesId)).ToList();
            var lstStudentNub = lstStudent.Select(p => p.StudentNumber).ToList();
            var lstAttendanceRecordByDate = _context.Set<AttendanceRecord>()
                .Where(c => lstStudentNub.Contains(c.UserNumber))
                .Where(c => c.AttendanceTime.Date == input.Date.Value.Date).ToList();
            foreach (var classes in lstClass)
            {
                var checkedTotalMorning = 0;
                var checkedTotalAfterno = 0;
                var _lstStudent = lstStudent.Where(p => p.ClassesId == classes.Id.ToString()).ToList();
                foreach (var student in _lstStudent)
                {
                    if (lstAttendanceRecordByDate.Any(p => p.UserNumber == student.StudentNumber && p.AttendanceTime.Hour <= 12))
                    {
                        checkedTotalMorning++;
                    }
                    if (lstAttendanceRecordByDate.Any(p => p.UserNumber == student.StudentNumber && p.AttendanceTime.Hour > 12))
                    {
                        checkedTotalAfterno++;
                    }
                }

                result.Morning.RecordDetails.Add(new CheckedInDetalDto()
                {
                    RowString = classes.Name,
                    Total = _lstStudent.Count,
                    CheckedTotal = checkedTotalMorning,
                    PunchRate = _lstStudent.Count == 0 ? "0.0" : (Convert.ToDouble((checkedTotalMorning / Convert.ToDouble(_lstStudent.Count)).ToString("F3") ?? "0") * 100).ToString("F1")
                });
                result.Afternoon.RecordDetails.Add(new CheckedInDetalDto()
                {
                    RowString = classes.Name,
                    Total = _lstStudent.Count,
                    CheckedTotal = checkedTotalAfterno,
                    PunchRate = _lstStudent.Count == 0 ? "0.0" : (Convert.ToDouble((checkedTotalAfterno / Convert.ToDouble(_lstStudent.Count)).ToString("F3") ?? "0") * 100).ToString("F1")
                });
            }
            result.Morning.Total = lstStudentNub?.Count ?? 0;
            result.Morning.CheckedTotal = result.Morning.RecordDetails?.Sum(c => c.CheckedTotal) ?? 0;
            result.Morning.PunchRate = result.Morning.RecordDetails.Sum(c => c.Total) == 0 ? "0.0" : (Convert.ToDouble((result.Morning.RecordDetails.Sum(c => c.CheckedTotal) / Convert.ToDouble(result.Morning.RecordDetails.Sum(c => c.Total))).ToString("F3") ?? "0") * 100).ToString("F1");
            result.Afternoon.Total = lstStudentNub?.Count ?? 0;
            result.Afternoon.CheckedTotal = result.Afternoon.RecordDetails?.Sum(c => c.CheckedTotal) ?? 0;
            result.Afternoon.PunchRate = result.Afternoon.RecordDetails.Sum(c => c.Total) == 0 ? "0.0" : (Convert.ToDouble((result.Afternoon.RecordDetails.Sum(c => c.CheckedTotal) / Convert.ToDouble(result.Afternoon.RecordDetails.Sum(c => c.Total))).ToString("F3") ?? "0") * 100).ToString("F1");

            return result;
        }

        public async Task<NotCheckedStatsDto> GetNotCheckedStats(NotCheckedStatsInput input)
        {
            var result = new NotCheckedStatsDto();
            var lstStudent = _context.Set<Childrens>().AsQueryable().Where(p => p.ClassesId == input.ClassId).ToList();
            if (!lstStudent.Any())
            {
                return result;
            }
            var lstStudentNub = lstStudent.Select(p => p.StudentNumber).ToList();
            var lstAttendanceRecordByDate = _context.Set<AttendanceRecord>()
                .Where(c => lstStudentNub.Contains(c.UserNumber))
                .Where(c => c.AttendanceTime.Date == input.Date.Value.Date).ToList();
            foreach (var student in lstStudent)
            {
                if (!lstAttendanceRecordByDate.Any(p => p.UserNumber == student.StudentNumber))
                {
                    result.ChildList.Add(student);
                }
            }
            result.NotCheckTotal = await NotCheckTotal(input.Date.Value);
            return result;
        }

        private async Task<int> NotCheckTotal(DateTime date)
        {
            var total = 0;
            var userId = _currentUser?.FindClaimValue(SimpleClaimTypes.UserId);
            if (userId == null)
            {
                return 0;
            }
            var lstClass = (await OrganizationFilter(_context.Set<SysClasses>().AsQueryable())).Where(p => p.TeacherIds.Contains(userId) && p.IsArchived == false).ToList();
            var lstClassId = lstClass.Select(p => p.Id.ToString()).ToList();
            if (lstClass == null || !lstClass.Any())
            {
                return 0;
            }
            var lstStudent = _context.Set<Childrens>().AsQueryable().Where(p => lstClassId.Contains(p.ClassesId)).ToList();
            var lstStudentNub = lstStudent.Select(p => p.StudentNumber).ToList();
            if (lstStudentNub == null)
            {
                return 0;
            }
            var lstAttendanceRecordByDate = _context.Set<AttendanceRecord>()
                .Where(c => lstStudentNub.Contains(c.UserNumber))
                .Where(c => c.AttendanceTime.Date == date.Date).ToList();
            foreach (var student in lstStudent)
            {
                if (!lstAttendanceRecordByDate.Any(p => p.UserNumber == student.StudentNumber))
                {
                    total++;
                }
            }
            return total;
        }

        public async Task<int> ManualCheck(ManualCheckInput input)
        {
            var student = _context.Set<Childrens>().AsQueryable().FirstOrDefault(p => p.Id.ToString() == input.ChildId);
            if (student == null)
            {
                throw AppResultException.Status409Conflict("没有找到幼儿");
            }
            var record = new AttendanceRecord()
            {
                UserId = student.Id,
                AttendanceTime = input.Date.Value,
                AttendanceType = 2,
                UserNumber = student.StudentNumber
            };
            _context.Add(record);
            return await _context.SaveChangesAsync();
        }

        public async Task<PageResultModel<StudentAttendanceModel>> GetClassAttendanceAsync(StudentAttendancePageInputModel input)
        {
            var result = new PageResultModel<StudentAttendanceModel>();
            if(string.IsNullOrEmpty(input.ClassId))
            {
                return result;
            }
            var sql = @"SELECT
	                a.StudentNumber,
	                a.NAME AS ChildName,
	                b.`Name` AS ClassName,
	                c.InAttendanceTime,
	                c.InAttendanceTime1,
	                c.OutAttendanceTime 
                FROM
	                `childrens` a
	                LEFT JOIN sysclasses b ON a.ClassesId = b.Id
	                INNER JOIN (
	                SELECT
		                UserNumber,
		                MIN( CASE WHEN EntranceType = 1 AND CardNo IS NULL THEN AttendanceTime ELSE NULL END ) AS InAttendanceTime,
		                MIN( CASE WHEN EntranceType = 1 AND CardNo IS NOT NULL THEN AttendanceTime ELSE NULL END ) AS InAttendanceTime1,
		                Max( CASE EntranceType WHEN 2 THEN AttendanceTime ELSE NULL END ) AS OutAttendanceTime 
	                FROM
		                attendancerecord 
	                WHERE
		                DATE( AttendanceTime ) = @AttendanceTime
		                AND AttendanceType = 2 
	                GROUP BY
		                UserNumber 
	                ) c ON a.StudentNumber = c.UserNumber 
                WHERE
	                a.ClassesId = @ClassesId
                ORDER BY
	                a.StudentNumber
	                ";

            result.Rows = _context.Database.SqlQuery<StudentAttendanceModel>(sql, new MySqlParameter("@ClassesId", input.ClassId)  , new MySqlParameter("@AttendanceTime", input.AttendanceTime.Date)).ToList();
            result.TotalRows = result.Rows.Count();
            return result;
        }
    }
}
